﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data;

namespace CustomsSystem.DAL
{
    public class WorkflowHandleService
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CustomsSystem.Model.workflow model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.sheetID != null)
            {
                strSql1.Append("sheetID,");
                strSql2.Append("" + model.sheetID + ",");
            }
            if (model.busID != null)
            {
                strSql1.Append("busID,");
                strSql2.Append("" + model.busID + ",");
            }
            if (model.busName != null)
            {
                strSql1.Append("busName,");
                strSql2.Append("'" + model.busName + "',");
            }
            if (model.jobNO != null)
            {
                strSql1.Append("jobNO,");
                strSql2.Append("'" + model.jobNO + "',");
            }
            if (model.inputEmpNo != null)
            {
                strSql1.Append("inputEmpNo,");
                strSql2.Append("'" + model.inputEmpNo + "',");
            }
            if (model.inputDate != null)
            {
                strSql1.Append("inputDate,");
                strSql2.Append("'" + model.inputDate + "',");
            }
            if (model.receiveMark != null)
            {
                strSql1.Append("receiveMark,");
                strSql2.Append("" + model.receiveMark + ",");
            }
            if (model.jobEmpNo != null)
            {
                strSql1.Append("jobEmpNo,");
                strSql2.Append("'" + model.jobEmpNo + "',");
            }
            if (model.recDate != null)
            {
                strSql1.Append("recDate,");
                strSql2.Append("'" + model.recDate + "',");
            }
            if (model.doDate != null)
            {
                strSql1.Append("doDate,");
                strSql2.Append("'" + model.doDate + "',");
            }
            if (model.doMark != null)
            {
                strSql1.Append("doMark,");
                strSql2.Append("'" + model.doMark + "',");
            }
            if (model.doEmpNo != null)
            {
                strSql1.Append("doEmpNo,");
                strSql2.Append("'" + model.doEmpNo + "',");
            }
            if (model.toDate != null)
            {
                strSql1.Append("toDate,");
                strSql2.Append("'" + model.toDate + "',");
            }
            if (model.ontimeType != null)
            {
                strSql1.Append("ontimeType,");
                strSql2.Append("" + model.ontimeType + ",");
            }
            if (model.infoWKID != null)
            {
                strSql1.Append("infoWKID,");
                strSql2.Append("" + model.infoWKID + ",");
            }
            if (model.Score != null)
            {
                strSql1.Append("score,");
                strSql2.Append("" + model.Score + ",");
            }
            strSql.Append("insert into T_workflow(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CustomsSystem.Model.workflow model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_workflow set ");
            if (model.sheetID != null)
            {
                strSql.Append("sheetID=" + model.sheetID + ",");
            }
            if (model.busID != null)
            {
                strSql.Append("busID=" + model.busID + ",");
            }
            if (model.busName != null)
            {
                strSql.Append("busName='" + model.busName + "',");
            }
            else
            {
                strSql.Append("busName= null ,");
            }
            if (model.jobNO != null)
            {
                strSql.Append("jobNO='" + model.jobNO + "',");
            }
            else
            {
                strSql.Append("jobNO= null ,");
            }
            if (model.inputEmpNo != null)
            {
                strSql.Append("inputEmpNo='" + model.inputEmpNo + "',");
            }
            else
            {
                strSql.Append("inputEmpNo= null ,");
            }
            if (model.inputDate != null)
            {
                strSql.Append("inputDate='" + model.inputDate + "',");
            }
            else
            {
                strSql.Append("inputDate= null ,");
            }
            if (model.receiveMark != null)
            {
                strSql.Append("receiveMark=" + model.receiveMark + ",");
            }
            else
            {
                strSql.Append("receiveMark= null ,");
            }
            if (model.jobEmpNo != null)
            {
                strSql.Append("jobEmpNo='" + model.jobEmpNo + "',");
            }
            else
            {
                strSql.Append("jobEmpNo= null ,");
            }
            if (model.recDate != null)
            {
                strSql.Append("recDate='" + model.recDate + "',");
            }
            else
            {
                strSql.Append("recDate= null ,");
            }
            if (model.doDate != null)
            {
                strSql.Append("doDate='" + model.doDate + "',");
            }
            else
            {
                strSql.Append("doDate= null ,");
            }
            if (model.doMark != null)
            {
                strSql.Append("doMark='" + model.doMark + "',");
            }
            else
            {
                strSql.Append("doMark= null ,");
            }
            if (model.doEmpNo != null)
            {
                strSql.Append("doEmpNo='" + model.doEmpNo + "',");
            }
            else
            {
                strSql.Append("doEmpNo= null ,");
            }
            if (model.toDate != null)
            {
                strSql.Append("toDate='" + model.toDate + "',");
            }
            else
            {
                strSql.Append("toDate= null ,");
            }
            if (model.ontimeType != null)
            {
                strSql.Append("ontimeType=" + model.ontimeType + ",");
            }
            else
            {
                strSql.Append("ontimeType= null ,");
            }
            if (model.infoWKID != null)
            {
                strSql.Append("infoWKID=" + model.infoWKID + ",");
            }
            else
            {
                strSql.Append("infoWKID= null ,");
            }
            if (model.Score != null)
            {
                strSql.Append("score=" + model.Score + ",");
            }
            else
            {
                strSql.Append("score= null ,");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where WKID=" + model.WKID + "");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int WKID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_workflow ");
            strSql.Append(" where WKID=" + WKID + "");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string WKIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_workflow ");
            strSql.Append(" where WKID in (" + WKIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.workflow GetModel(int WKID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1  ");
            strSql.Append(" WKID,sheetID,busID,busName,jobNO,inputEmpNo,inputDate,receiveMark,jobEmpNo,recDate,doDate,doMark,doEmpNo,toDate,ontimeType,infoWKID,score");
            strSql.Append(" from T_workflow ");
            strSql.Append(" where WKID=" + WKID + "");
            CustomsSystem.Model.workflow model = new CustomsSystem.Model.workflow();
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CustomsSystem.Model.workflow DataRowToModel(DataRow row)
        {
            CustomsSystem.Model.workflow model = new CustomsSystem.Model.workflow();
            if (row != null)
            {
                if (row["WKID"] != null && row["WKID"].ToString() != "")
                {
                    model.WKID = int.Parse(row["WKID"].ToString());
                }
                if (row["sheetID"] != null && row["sheetID"].ToString() != "")
                {
                    model.sheetID = int.Parse(row["sheetID"].ToString());
                }
                if (row["busID"] != null && row["busID"].ToString() != "")
                {
                    model.busID = int.Parse(row["busID"].ToString());
                }
                if (row["busName"] != null)
                {
                    model.busName = row["busName"].ToString();
                }
                if (row["jobNO"] != null)
                {
                    model.jobNO = row["jobNO"].ToString();
                }
                if (row["inputEmpNo"] != null)
                {
                    model.inputEmpNo = row["inputEmpNo"].ToString();
                }
                if (row["inputDate"] != null && row["inputDate"].ToString() != "")
                {
                    model.inputDate = DateTime.Parse(row["inputDate"].ToString());
                }
                if (row["receiveMark"] != null && row["receiveMark"].ToString() != "")
                {
                    model.receiveMark = int.Parse(row["receiveMark"].ToString());
                }
                if (row["jobEmpNo"] != null)
                {
                    model.jobEmpNo = row["jobEmpNo"].ToString();
                }
                if (row["recDate"] != null && row["recDate"].ToString() != "")
                {
                    model.recDate = DateTime.Parse(row["recDate"].ToString());
                }
                if (row["doDate"] != null && row["doDate"].ToString() != "")
                {
                    model.doDate = DateTime.Parse(row["doDate"].ToString());
                }
                if (row["doMark"] != null)
                {
                    model.doMark = row["doMark"].ToString();
                }
                if (row["doEmpNo"] != null)
                {
                    model.doEmpNo = row["doEmpNo"].ToString();
                }
                if (row["toDate"] != null && row["toDate"].ToString() != "")
                {
                    model.toDate = DateTime.Parse(row["toDate"].ToString());
                }
                if (row["ontimeType"] != null && row["ontimeType"].ToString() != "")
                {
                    model.ontimeType = int.Parse(row["ontimeType"].ToString());
                }
                if (row["infoWKID"] != null && row["infoWKID"].ToString() != "")
                {
                    model.infoWKID = int.Parse(row["infoWKID"].ToString());
                }
                if (row["score"] != null && row["score"].ToString() != "")
                {
                    model.Score = int.Parse(row["score"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *");
            strSql.Append(" FROM T_workflow ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取多表数据的视图
        /// </summary>
        /// <returns></returns>
        public DataSet GetView(string strWhere)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("select WKID,sheetID,busID,busName,jobName,emp.UserName,emp2.UserName,emp3.UserName,inputDate,receiveMark,recDate,doDate,doMark,toDate,ontimeType,infoWKID,score ");
            strsql.Append("from T_workflow wf left join Info_job job on (wf.jobNO=job.JobNo) left join Cu_User emp on(wf.inputEmpNo=emp.EMPNum) left join Cu_User emp2 on(wf.jobEmpNo=emp2.EMPNum) left join Cu_User emp3 on(wf.doEmpNo=emp3.EMPNum) ");
            if (strWhere != "")
            {
                strsql.Append("where " + strWhere);
            }
            return DbHelperSQL.Query(strsql.ToString());
        }
        /// <summary>
        /// 获取统计的视图
        /// </summary>
        /// <returns></returns>
        public DataSet GetCountView(string strWhere)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append(" with sheet as( select sheetID, ladingBillNo,agencyDate,shipName,goodsName,cus.CustomerName from T_sheetInfo she left join Cu_Customer cus on(she.cID=cus.ID))  ");
            strsql.Append(" select sheet.*, WKID,busID,busName,jobName,emp.UserName,emp2.UserName,emp3.UserName,wf.inputDate,receiveMark,recDate,doDate,doMark,toDate,ontimeType,infoWKID,score  ");
            strsql.Append("from T_workflow wf left join Info_job job on (wf.jobNO=job.JobNo) left join Cu_User emp on(wf.inputEmpNo=emp.EMPNum) left join Cu_User emp2 on(wf.jobEmpNo=emp2.EMPNum) left join Cu_User emp3 on(wf.doEmpNo=emp3.EMPNum) left join sheet on (sheet.sheetID=wf.sheetID) ");
            if (strWhere != "")
            {
                strsql.Append("where " + strWhere);
            }
            return DbHelperSQL.Query(strsql.ToString());
        }
        /// <summary>
        /// 判断是否超时
        /// </summary>
        /// <param name="sheedID"></param>
        /// <returns></returns>
        public int GetIntimeFlag(string sheedID, int type)
        {
            if(type == 1)
                return DbHelperSQL.RunMyProcedure(sheedID);
            else if(type == 2)
                return DbHelperSQL.RunMyProcedure2(sheedID);
            else 
                return 1;
        }
    }
}
